const express = require('express');
const router = express.Router();
const mysql = require('mysql2/promise');
const dbConfig = require('../config/database');

// 获取所有借阅记录（带分页和排序）
router.get('/', async (req, res) => {
    try {
        const page = parseInt(req.query.page) || 1;
        const limit = parseInt(req.query.limit) || 5;
        const offset = (page - 1) * limit;
        const sortColumn = req.query.sortColumn || 'borrow_date';
        const sortDirection = req.query.sortDirection || 'desc';

        const connection = await mysql.createConnection(dbConfig);

        const [countResult] = await connection.execute('SELECT COUNT(*) as total FROM borrowings');
        const total = countResult[0].total;

        const [rows] = await connection.execute(`
            SELECT b.*, 
                   books.title as book_title,
                   users.name as user_name
            FROM borrowings b
            JOIN books ON b.book_id = books.id
            JOIN users ON b.user_id = users.id
            ORDER BY b.${sortColumn} ${sortDirection}
            LIMIT ${limit} OFFSET ${offset}
        `);

        await connection.end();

        res.json({
            data: rows,
            pagination: {
                page,
                limit,
                total,
                totalPages: Math.ceil(total / limit)
            }
        });
    } catch (error) {
        console.error('获取借阅记录失败:', error);
        res.status(500).json({ error: '获取借阅记录失败' });
    }
});

// 搜索借阅记录
router.get('/search', async (req, res) => {
    try {
        const { q } = req.query;
        const connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute(`
            SELECT b.*, 
                   books.title as book_title,
                   users.name as user_name
            FROM borrowings b
            JOIN books ON b.book_id = books.id
            JOIN users ON b.user_id = users.id
            WHERE books.title LIKE ? OR users.name LIKE ?
            ORDER BY b.borrow_date DESC
        `, [`%${q}%`, `%${q}%`]);
        await connection.end();
        res.json(rows);
    } catch (error) {
        res.status(500).json({ error: '搜索借阅记录失败' });
    }
});

// 新增借阅
router.post('/', async (req, res) => {
    const connection = await mysql.createConnection(dbConfig);
    try {
        const { book_id, user_id, return_date } = req.body;

        // 输入验证
        if (!book_id || !user_id || !return_date) {
            return res.status(400).json({ error: '所有字段都是必填的' });
        }

        await connection.beginTransaction();

        // 检查图书是否存在且有库存
        const [books] = await connection.execute(
            'SELECT stock FROM books WHERE id = ?',
            [book_id]
        );

        if (books.length === 0) {
            throw new Error('图书不存在');
        }

        if (books[0].stock <= 0) {
            throw new Error('图书库存不足');
        }

        // 检查用户是否存在
        const [users] = await connection.execute(
            'SELECT id FROM users WHERE id = ?',
            [user_id]
        );

        if (users.length === 0) {
            throw new Error('用户不存在');
        }

        // 创建借阅记录
        const [result] = await connection.execute(
            'INSERT INTO borrowings (book_id, user_id, return_date) VALUES (?, ?, ?)',
            [book_id, user_id, return_date]
        );

        // 更新图书库存
        await connection.execute(
            'UPDATE books SET stock = stock - 1 WHERE id = ?',
            [book_id]
        );

        await connection.commit();

        // 获取完整的借阅信息
        const [borrowing] = await connection.execute(`
            SELECT b.*, 
                   books.title as book_title,
                   users.name as user_name
            FROM borrowings b
            JOIN books ON b.book_id = books.id
            JOIN users ON b.user_id = users.id
            WHERE b.id = ?
        `, [result.insertId]);

        res.status(201).json(borrowing[0]);
    } catch (error) {
        await connection.rollback();
        console.error('新增借阅失败:', error);
        res.status(500).json({
            error: '新增借阅失败',
            details: error.message
        });
    } finally {
        await connection.end();
    }
});

// 归还图书
router.put('/:id/return', async (req, res) => {
    const connection = await mysql.createConnection(dbConfig);
    try {
        await connection.beginTransaction();

        // 获取借阅记录
        const [borrowResult] = await connection.execute(
            'SELECT * FROM borrowings WHERE id = ? AND status = "borrowed"',
            [req.params.id]
        );

        if (borrowResult.length === 0) {
            throw new Error('借阅记录不存在或图书已归还');
        }

        // 更新借阅状态
        await connection.execute(
            'UPDATE borrowings SET status = "returned" WHERE id = ?',
            [req.params.id]
        );

        // 更新图书库存
        await connection.execute(
            'UPDATE books SET stock = stock + 1 WHERE id = ?',
            [borrowResult[0].book_id]
        );

        await connection.commit();
        res.json({ message: '图书归还成功' });
    } catch (error) {
        await connection.rollback();
        res.status(400).json({ error: error.message });
    } finally {
        await connection.end();
    }
});

// 获取借阅统计信息
router.get('/stats', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        const today = new Date().toISOString().split('T')[0];
        const sevenDaysLater = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000).toISOString().split('T')[0];

        // 获取当前借出数量
        const [currentBorrowed] = await connection.execute(
            'SELECT COUNT(*) as count FROM borrowings WHERE status = "borrowed"'
        );

        // 获取即将到期数量（7天内）
        const [soonExpire] = await connection.execute(
            'SELECT COUNT(*) as count FROM borrowings WHERE status = "borrowed" AND return_date BETWEEN ? AND ?',
            [today, sevenDaysLater]
        );

        // 获取已逾期数量
        const [overdue] = await connection.execute(
            'SELECT COUNT(*) as count FROM borrowings WHERE status = "borrowed" AND return_date < ?',
            [today]
        );

        await connection.end();
        res.json({
            currentBorrowed: currentBorrowed[0].count,
            soonExpire: soonExpire[0].count,
            overdue: overdue[0].count
        });
    } catch (error) {
        res.status(500).json({ error: '获取统计信息失败' });
    }
});

// 导出借阅数据
router.get('/export', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute(`
            SELECT b.*, 
                   books.title as book_title,
                   users.name as user_name
            FROM borrowings b
            JOIN books ON b.book_id = books.id
            JOIN users ON b.user_id = users.id
            ORDER BY b.borrow_date DESC
        `);
        await connection.end();

        // 生成 CSV 数据
        const csvHeader = '借阅ID,书名,借阅人,借阅日期,应还日期,状态\n';
        const csvData = rows.map(borrowing =>
            `${borrowing.id},"${borrowing.book_title}","${borrowing.user_name}",` +
            `"${borrowing.borrow_date}","${borrowing.return_date}","${borrowing.status}"`
        ).join('\n');

        res.setHeader('Content-Type', 'text/csv; charset=utf-8');
        res.setHeader('Content-Disposition', 'attachment; filename=borrowings.csv');
        res.send(csvHeader + csvData);
    } catch (error) {
        res.status(500).json({ error: '导出数据失败' });
    }
});

// 删除借阅记录
router.delete('/:id', async (req, res) => {
    const connection = await mysql.createConnection(dbConfig);
    try {
        await connection.beginTransaction();

        // 获取借阅记录
        const [borrowing] = await connection.execute(
            'SELECT * FROM borrowings WHERE id = ?',
            [req.params.id]
        );

        if (borrowing.length === 0) {
            throw new Error('借阅记录不存在');
        }

        // 如果是已借出状态，需要归还库存
        if (borrowing[0].status === 'borrowed') {
            await connection.execute(
                'UPDATE books SET stock = stock + 1 WHERE id = ?',
                [borrowing[0].book_id]
            );
        }

        // 删除借阅记录
        await connection.execute(
            'DELETE FROM borrowings WHERE id = ?',
            [req.params.id]
        );

        await connection.commit();
        res.status(204).end();
    } catch (error) {
        await connection.rollback();
        console.error('删除借阅记录失败:', error);
        res.status(500).json({
            error: '删除借阅记录失败',
            details: error.message
        });
    } finally {
        await connection.end();
    }
});

module.exports = router; 